import os
import pandas as pd
from datetime import datetime
import time

def get_quarter(date_str):
    """根据日期字符串返回季度"""
    if pd.isna(date_str):
        return None
    try:
        date = pd.to_datetime(date_str)
        return (date.month - 1) // 3 + 1
    except:
        return None

def check_files_exist():
    """检查所需文件是否存在"""
    required_files = [
        "年初糖尿病名单.xlsx",
        "年底糖尿病在管+死亡名单.xlsx",
        "糖尿病随访明细.xlsx",
        "终止名单.xlsx"
    ]
    missing_files = [f for f in required_files if not os.path.exists(os.path.join("所需数据", f))]
    if missing_files:
        print(f"错误：缺少以下文件: {', '.join(missing_files)}")
        return False
    return True

def main():
    print("📊【慢病管理名单核对（按村）V2】 - 2025.11.10📊")
    print("=" * 80)
    print("功能说明:")
    print(" -1. 📊年初与年底名单核对")
    print(" -2. 📊匹配终止名单及终止日期")
    print(" -3. 📊统计各患者的季度随访次数")
    print(" -4. 📊判断新增季度")
    print(" -5. 📊生成全年管理名单")
    print(" -6. 📊判断终止当天或之后的随访")
    print(" -7. 📊判断是否需要复核")
    print('-' * 50)
    print("开始糖尿病名单核查...")
    start_time = time.time()
    
    # 检查文件是否存在
    if not check_files_exist():
        return
    
    # 创建核查结果文件夹
    os.makedirs("核查结果", exist_ok=True)
    
    # 生成输出文件名
    timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
    output_file = os.path.join("核查结果", f"糖尿病名单核查_{timestamp}.xlsx")
    
    print("正在读取数据文件...")
    
    # 读取所有数据文件
    try:
        # 读取年初名单
        df_start = pd.read_excel(os.path.join("所需数据", "年初糖尿病名单.xlsx"), dtype={
            '健康档案号': str,
            '身份证号': str
        })
        print(" - 已读取年初糖尿病名单")
        
        # 读取年底名单
        df_end = pd.read_excel(os.path.join("所需数据", "年底糖尿病在管+死亡名单.xlsx"), dtype={
            '健康档案号': str,
            '身份证号': str
        })
        print(" - 已读取年底糖尿病在管+死亡名单")
        
        # 读取随访明细
        df_follow = pd.read_excel(os.path.join("所需数据", "糖尿病随访明细.xlsx"), dtype={
            '身份证号': str
        })
        print(" - 已读取糖尿病随访明细")
        
        # 读取终止名单
        df_terminate = pd.read_excel(os.path.join("所需数据", "终止名单.xlsx"), dtype={
            '个人健康档案号': str,
            '身份证号': str
        })
        print(" - 已读取终止名单")
    except Exception as e:
        print(f"读取文件时出错: {str(e)}")
        return
    
    # 获取所有身份证号
    all_ids = set(df_start['身份证号'].unique()).union(set(df_end['身份证号'].unique()))
    print(f" - 共找到 {len(all_ids)} 个唯一身份证号")
    
    # 创建结果DataFrame
    print("正在合并数据...")
    result_df = pd.DataFrame({'身份证号': list(all_ids)})

    # 首先合并年初数据
    result_df = result_df.merge(df_start, on='身份证号', how='left')

    # 添加年初名单是否存在列
    result_df['年初名单是否存在'] = result_df['健康档案号'].apply(lambda x: '是' if pd.notna(x) else '否')

    # 添加年底名单是否存在列
    result_df['年底名单是否存在'] = result_df['身份证号'].apply(lambda x: '是' if x in df_end['身份证号'].values else '否')

    # 对于年初不存在但年底存在的记录，从年底名单中补充数据
    print("正在补充新增患者数据...")
    # 获取需要补充数据的身份证号
    need_update_ids = result_df[(result_df['年初名单是否存在'] == '否') & (result_df['年底名单是否存在'] == '是')]['身份证号']

    if not need_update_ids.empty:
        # 从年底名单中获取需要补充的数据
        update_data = df_end[df_end['身份证号'].isin(need_update_ids)]
        
        # 找出年初和年底名单共有的列（除了身份证号）
        common_columns = set(df_start.columns).intersection(set(df_end.columns))
        common_columns.discard('身份证号')
        
        print(f" - 将补充以下列的数据: {', '.join(common_columns)}")
        
        # 补充数据到结果DataFrame中
        for col in common_columns:
            # 创建一个映射字典：身份证号 -> 列值
            mapping = update_data.set_index('身份证号')[col].to_dict()
            # 更新结果DataFrame中的对应列
            result_df[col] = result_df.apply(
                lambda row: mapping[row['身份证号']] if (row['年初名单是否存在'] == '否' and 
                                                     row['年底名单是否存在'] == '是' and 
                                                     row['身份证号'] in mapping) else row[col],
                axis=1
            )

    # 添加是否当年新增列
    result_df['是否当年新增'] = result_df.apply(
        lambda row: '是' if row['年初名单是否存在'] == '否' and row['年底名单是否存在'] == '是' else '', axis=1)
    
    print("正在计算随访次数...")
    
    # 处理随访数据
    if not df_follow.empty:
        df_follow['随访日期'] = pd.to_datetime(df_follow['随访日期'], errors='coerce')
        df_follow = df_follow.dropna(subset=['随访日期'])
        df_follow['季度'] = df_follow['随访日期'].apply(get_quarter)
    
    # 初始化随访次数列
    for q in range(1, 5):
        result_df[f'{q}季度随访次数'] = ''
    
    # 计算每个季度的随访次数
    for idx, row in result_df.iterrows():
        if row['年初名单是否存在'] == '是' and row['年底名单是否存在'] == '是':
            continue
        
        id_card = row['身份证号']
        if id_card in df_follow['身份证号'].values:
            for q in range(1, 5):
                count = len(df_follow[(df_follow['身份证号'] == id_card) & (df_follow['季度'] == q)])
                if count > 0:
                    result_df.at[idx, f'{q}季度随访次数'] = count
    
    print("正在计算预判新增季度...")
    
    # 添加预判新增季度列
    result_df['预判新增季度'] = ''
    for idx, row in result_df.iterrows():
        if row['年初名单是否存在'] == '是' and row['年底名单是否存在'] == '是':
            continue
        
        for q in range(1, 5):
            if pd.notna(row[f'{q}季度随访次数']) and row[f'{q}季度随访次数'] != '':
                result_df.at[idx, '预判新增季度'] = q
                break
    
    print("正在处理终止信息...")
    
    # 添加终止状态和终止日期列
    result_df['终止状态'] = ''
    result_df['终止日期'] = ''
    
    for idx, row in result_df.iterrows():
        id_card = row['身份证号']
        if id_card in df_terminate['身份证号'].values:
            terminate_info = df_terminate[df_terminate['身份证号'] == id_card].iloc[0]
            result_df.at[idx, '终止状态'] = '是'
            death_time = terminate_info['死亡时间']
            if pd.isna(death_time):
                result_df.at[idx, '终止日期'] = '不详'
            else:
                result_df.at[idx, '终止日期'] = death_time
    
    print("正在检查终止后随访和录入...")
    
    # 添加是否存在终止后随访和录入列
    result_df['是否存在终止后随访'] = ''
    result_df['是否存在终止后录入'] = ''
    
    if not df_follow.empty:
        df_follow['录入时间'] = pd.to_datetime(df_follow['录入时间'], errors='coerce')
    
    for idx, row in result_df.iterrows():
        if row['终止状态'] != '是':
            continue
        
        id_card = row['身份证号']
        death_date = row['终止日期']
        
        if death_date == '不详':
            continue
        
        try:
            death_date = pd.to_datetime(death_date)
            
            # 检查终止后随访
            follow_after = df_follow[
                (df_follow['身份证号'] == id_card) & 
                (df_follow['随访日期'] >= death_date)
            ]
            if not follow_after.empty:
                result_df.at[idx, '是否存在终止后随访'] = '是'
            
            # 检查终止后录入
            input_after = df_follow[
                (df_follow['身份证号'] == id_card) & 
                (df_follow['录入时间'] >= death_date)
            ]
            if not input_after.empty:
                result_df.at[idx, '是否存在终止后录入'] = '是'
        except:
            continue
    
    print("正在检查需要复核丢失原因的情况...")
    
    # 添加是否需要复核丢失原因列
    result_df['是否需要复核丢失原因'] = ''
    for idx, row in result_df.iterrows():
        if (row['年初名单是否存在'] == '是' and 
            row['年底名单是否存在'] == '否' and 
            row['终止状态'] != '是'):
            result_df.at[idx, '是否需要复核丢失原因'] = '是'
    
    # 重新排列列顺序
    # 首先获取年初名单的所有列
    start_cols = list(df_start.columns)
    # 然后添加我们新增的列
    additional_cols = [
        '年初名单是否存在', '年底名单是否存在', '是否当年新增',
        '1季度随访次数', '2季度随访次数', '3季度随访次数', '4季度随访次数',
        '预判新增季度', '终止状态', '终止日期', 
        '是否存在终止后随访', '是否存在终止后录入', '是否需要复核丢失原因'
    ]
    # 合并所有列
    cols = start_cols + additional_cols
    
    # 确保结果包含所有需要的列
    result_df = result_df.reindex(columns=cols)
    
    print("正在保存结果文件...")
    
    # 保存结果
    try:
        with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
            result_df.to_excel(writer, index=False)
        print(f" - 已保存到: {output_file}")
    except Exception as e:
        print(f"保存结果文件时出错: {str(e)}")
        return
    
    end_time = time.time()
    print(f" - 总耗时: {end_time - start_time:.2f}秒")

if __name__ == "__main__":
    main()

#################################################################################
import os
import pandas as pd
from datetime import datetime
import glob
from tqdm import tqdm
import openpyxl

def find_latest_excel_file(folder_path):
    # 查找所有Excel文件
    excel_files = glob.glob(os.path.join(folder_path, "*.xlsx")) + \
                  glob.glob(os.path.join(folder_path, "*.xls"))
    
    # 过滤以'糖尿病名单核查'开头的文件
    target_files = []
    for f in excel_files:
        filename = os.path.basename(f)
        if filename.startswith('糖尿病名单核查'):
            target_files.append(f)
    
    if not target_files:
        raise FileNotFoundError("未找到以'糖尿病名单核查'开头的Excel文件")
    
    latest_file = max(target_files, key=os.path.getmtime)
    
    return latest_file

def check_required_columns(df):
    required_columns = [
        '姓名', '身份证号', '所属管辖地', '年初名单是否存在', '年底名单是否存在',
        '是否当年新增', '1季度随访次数', '2季度随访次数', '3季度随访次数', 
        '4季度随访次数', '预判新增季度', '终止状态', '终止日期', 
        '是否存在终止后随访', '是否存在终止后录入', '是否需要复核丢失原因'
    ]
    
    missing_columns = [col for col in required_columns if col not in df.columns]
    
    if missing_columns:
        raise ValueError(f"文件缺少必需的列: {missing_columns}")
    
    return True

def process_excel_file(file_path):
    print(f"正在读取文件: {os.path.basename(file_path)}")
    
    df = pd.read_excel(file_path)
    
    check_required_columns(df)
    
    # 确定分组列
    if '区镇' in df.columns and not df['区镇'].isna().all():
        group_column = '区镇'
        print(" - 使用'区镇'列进行分组统计")
    else:
        group_column = '所属管辖地'
        print(" - 使用'所属管辖地'列进行分组统计")
    
    # 预处理数据
    df = preprocess_data(df)
    
    # 进行统计
    statistics_df = calculate_statistics(df, group_column)
    
    return statistics_df

def preprocess_data(df):
    df_processed = df.copy()
    
    if '预判新增季度' in df_processed.columns:
        df_processed['预判新增季度'] = df_processed['预判新增季度'].astype(str).str.strip()
        df_processed['预判新增季度'] = df_processed['预判新增季度'].str.replace(r'\.0$', '', regex=True)
        df_processed['预判新增季度'] = df_processed['预判新增季度'].replace(['nan', 'None', 'NULL', ''], pd.NA)
    
    # 处理终止日期列 - 处理文本和日期格式
    if '终止日期' in df_processed.columns:
        df_processed['终止日期'] = pd.to_datetime(df_processed['终止日期'], errors='coerce')
        
        if df_processed['终止日期'].isna().all():
            date_formats = ['%Y/%m/%d', '%Y-%m-%d', '%Y.%m.%d', '%Y年%m月%d日',
                           '%m/%d/%Y', '%m-%d-%Y', '%d/%m/%Y', '%d-%m-%Y']
            
            for fmt in date_formats:
                try:
                    temp_dates = pd.to_datetime(df_processed['终止日期'], format=fmt, errors='coerce')
                    if not temp_dates.isna().all():
                        df_processed['终止日期'] = temp_dates
                        break
                except:
                    continue
    
    return df_processed

def calculate_statistics(df, group_column):
    statistics_data = []
    
    # 获取唯一的分组值
    groups = df[group_column].unique()
    
    print("正在进行统计计算...")
    for group in tqdm(groups, desc="  - 进度"):
        group_data = df[df[group_column] == group]
        
        # 1. 年初管理人数
        start_year_count = len(group_data[group_data['年初名单是否存在'] == '是'])
        
        # 2. 年底管理人数_含死亡
        end_year_all_count = len(group_data[group_data['年底名单是否存在'] == '是'])
        
        # 3. 年底管理人数_正常
        end_year_normal_count = len(group_data[
            (group_data['年底名单是否存在'] == '是') & 
            (group_data['终止状态'].isna() | (group_data['终止状态'] == ''))
        ])
        
        # 4. 年底管理人数_死亡
        end_year_death_count = len(group_data[
            (group_data['年底名单是否存在'] == '是') & 
            (group_data['终止状态'] == '是')
        ])
        
        # 5. 全年新增人数
        new_year_count = len(group_data[group_data['预判新增季度'].notna() & (group_data['预判新增季度'] != '')])
        
        # 6-9. 季度新增人数 - 处理文本和数字格式的季度值
        new_q1_count = len(group_data[group_data['预判新增季度'].astype(str).str.strip().isin(['1', '1.0', '一季度', '第1季度'])])
        new_q2_count = len(group_data[group_data['预判新增季度'].astype(str).str.strip().isin(['2', '2.0', '二季度', '第2季度'])])
        new_q3_count = len(group_data[group_data['预判新增季度'].astype(str).str.strip().isin(['3', '3.0', '三季度', '第3季度'])])
        new_q4_count = len(group_data[group_data['预判新增季度'].astype(str).str.strip().isin(['4', '4.0', '四季度', '第4季度'])])
        
        # 10. 全年死亡人数
        death_year_count = len(group_data[group_data['终止状态'] == '是'])
        
        # 11-14. 季度死亡人数 - 处理日期格式
        death_q1_count = 0
        death_q2_count = 0
        death_q3_count = 0
        death_q4_count = 0
        
        if '终止日期' in group_data.columns:
            # 过滤出有效的日期
            valid_dates = group_data[group_data['终止日期'].notna()]
            
            # 使用pd.Period判断季度
            for idx, row in valid_dates.iterrows():
                try:
                    if pd.notna(row['终止日期']):
                        # 确保是Timestamp类型
                        if hasattr(row['终止日期'], 'quarter'):
                            quarter = row['终止日期'].quarter
                            if quarter == 1:
                                death_q1_count += 1
                            elif quarter == 2:
                                death_q2_count += 1
                            elif quarter == 3:
                                death_q3_count += 1
                            elif quarter == 4:
                                death_q4_count += 1
                except:
                    continue
        
        # 15. 死亡后随访人数
        follow_after_death_count = len(group_data[group_data['是否存在终止后随访'] == '是'])
        
        # 16. 死亡后录入人数
        input_after_death_count = len(group_data[group_data['是否存在终止后录入'] == '是'])
        
        # 17. 需复核人数
        review_count = len(group_data[group_data['是否需要复核丢失原因'] == '是'])
        
        statistics_data.append({
            group_column: group,
            '年初管理人数': start_year_count,
            '年底管理人数_含死亡': end_year_all_count,
            '年底管理人数_正常': end_year_normal_count,
            '年底管理人数_死亡': end_year_death_count,
            '全年新增人数': new_year_count,
            '1季度新增人数': new_q1_count,
            '2季度新增人数': new_q2_count,
            '3季度新增人数': new_q3_count,
            '4季度新增人数': new_q4_count,
            '全年死亡人数': death_year_count,
            '1季度死亡人数': death_q1_count,
            '2季度死亡人数': death_q2_count,
            '3季度死亡人数': death_q3_count,
            '4季度死亡人数': death_q4_count,
            '死亡后随访人数': follow_after_death_count,
            '死亡后录入人数': input_after_death_count,
            '需复核人数': review_count
        })
    
    # 创建统计结果DataFrame
    statistics_df = pd.DataFrame(statistics_data)
    
    # 添加合计行
    total_data = {group_column: '合计'}
    for col in statistics_df.columns:
        if col != group_column:
            total_data[col] = statistics_df[col].sum()
    
    total_df = pd.DataFrame([total_data])
    statistics_df = pd.concat([statistics_df, total_df], ignore_index=True)
    
    return statistics_df

def save_statistics_with_formatting(statistics_df, original_file_path, output_folder):
    original_filename = os.path.basename(original_file_path)
    name_without_ext = os.path.splitext(original_filename)[0]
    output_filename = f"统计{name_without_ext}.xlsx"
    output_path = os.path.join(output_folder, output_filename)
    
    # 创建Excel写入器
    with pd.ExcelWriter(output_path, engine='openpyxl') as writer:
        statistics_df.to_excel(writer, sheet_name='统计结果', index=False)
        
        workbook = writer.book
        worksheet = writer.sheets['统计结果']
        
        # 设置列宽
        from openpyxl.utils import get_column_letter
        for idx, col in enumerate(worksheet.columns, 1):
            column_letter = get_column_letter(idx)
            if idx == 1:  # 第一列
                worksheet.column_dimensions[column_letter].width = 15
            else:
                worksheet.column_dimensions[column_letter].width = 8
        
        # 设置表头自动换行
        header_row = worksheet[1]
        for cell in header_row:
            cell.alignment = openpyxl.styles.Alignment(wrap_text=True)
        
        # 设置所有单元格边框
        from openpyxl.styles import Border, Side
        
        thin_border = Border(
            left=Side(style='thin'), 
            right=Side(style='thin'), 
            top=Side(style='thin'), 
            bottom=Side(style='thin')
        )
        
        # 应用边框到所有有数据的单元格
        for row in worksheet.iter_rows(
            min_row=1, 
            max_row=worksheet.max_row, 
            min_col=1, 
            max_col=worksheet.max_column
        ):
            for cell in row:
                cell.border = thin_border
    
    return output_path

def main():
    try:
        # 设置工作目录
        current_dir = os.getcwd()
        check_folder = os.path.join(current_dir, "核查结果")
        
        if not os.path.exists(check_folder):
            raise FileNotFoundError(f"文件夹'{check_folder}'不存在")
        
        print("正在查找最新的Excel文件...")
        
        # 查找最新的Excel文件
        latest_file = find_latest_excel_file(check_folder)
        print(f" - 最新文件: {os.path.basename(latest_file)}")
        print(f" - 文件路径: {latest_file}")
        
        # 处理Excel文件
        statistics_df = process_excel_file(latest_file)
        
        # 保存统计结果
        output_path = save_statistics_with_formatting(statistics_df, latest_file, check_folder)
        print(f"  - 已保存到: {output_path}")
        print("=" * 80)
        print('处理完成！')
        print("感谢你的使用")
        print("           --郁进 136****8090 微信同号")
        print("=" * 80)
        
    except Exception as e:
        print(f"处理过程中出现错误: {str(e)}")
        return False
    
    return True

if __name__ == "__main__":
    main()